SELECT * FROM oilback.sales_info;

show create table sales_info;

alter table sales_info drop foreign key sales_info_ibfk_1;
alter table sales_info drop foreign key sales_info_ibfk_2;
alter table sales_info drop foreign key sales_info_ibfk_3;

alter table oilback.sales_info add constraint FK_sales_oil_id foreign key (sales_oil_id) references oil_type(oil_id)on update cascade on delete cascade;

alter table oilback.sales_info add constraint FK_sales_deopt_id foreign key (sales_deopt_id) references oildepot_info(depot_id)on update cascade on delete cascade;

alter table oilback.sales_info add constraint FK_sales_worker foreign key (sales_worker) references worker_info(worker_id)on update cascade on delete cascade;

DELIMITER $$
DROP TRIGGER IF EXISTS test1.sc_AFTER_INSERT$$   # 如果有的话就删除该触发器 		test1 是数据库名 

CREATE  TRIGGER test1.sc_AFTER_INSERT
 before INSERT ON sc FOR EACH ROW
BEGIN
IF new.sno  NOT IN (SELECT sno FROM student)  OR new.cno NOT IN (SELECT cno  FROM course )
Then	
  signal sqlstate '45000' set message_text ='错误！违背数据的一致性！' ;
 end if;   
 END$$

DELIMITER ;

DELIMITER $$
CREATE  TRIGGER oilback.reserves_AFTER_INSERT
after INSERT ON purchase_info FOR EACH ROW
BEGIN
IF new.purchase_oil  IN (SELECT oil_id FROM oil_type)  and new.purchase_storage_depot  IN (SELECT depot_id  FROM oildepot_info )
Then	
   update oil_reserves_info
   set oil_reserves = oil_reserves + new.purchase_amount
   where new.purchase_storage_depot = oil_reserves_info.depot_oil_id and new.purchase_oil = oil_reserves_info.oil_id;
 end if;   
 END$$
DELIMITER ;



DELIMITER $$
CREATE  TRIGGER oilback.reserves_AFTER_INSERT
after INSERT ON purchase_info FOR EACH ROW
BEGIN	
   update oil_reserves_info
   set oil_reserves = oil_reserves + new.purchase_amount
   where new.purchase_storage_depot = oil_reserves_info.depot_oil_id and new.purchase_oil = oil_reserves_info.oil_id;
 END$$
DELIMITER ;


insert 
into purchase_info
values('2022Y05M01D18H', '2022-05-03 18:56:02', '1', '3', 'ZSH20011394', '6666', '6.78') 


DELIMITER $$
DROP TRIGGER IF EXISTS oilback.reserves_before_sales_insert$$   # 如果有的话就删除该触发器

CREATE  TRIGGER oilback.reserves_before_sales_insert
before INSERT ON sales_info FOR EACH ROW
BEGIN	
	if new.sales_amount>(select oil_reserves from oil_reserves_info where new.sales_oil_id=oil_reserves_info.oil_id and new.sales_deopt_id=oil_reserves_info.depot_oil_id)
    then signal sqlstate '45000' set message_text ='错误！对应油库已经没有足够的该类油出售！' ;
    else
		update oil_reserves_info
		set oil_reserves = oil_reserves - new.sales_amount
		where new.sales_deopt_id = oil_reserves_info.depot_oil_id and new.sales_oil_id = oil_reserves_info.oil_id;
   end if;
 END$$
DELIMITER ;

insert 
into sales_info(sales_order_id, sales_time, sales_oil_id, sales_deopt_id, sales_amount, sales_price, sales_worker, sales_customer, sales_discount)
values(
'ZSY2089Z34A799', '2022-06-01 08:10:09', '5', '1', '66.00', '9.12', 'ZSH20000406', '牛马', '8.00'
);



DELIMITER $$
DROP TRIGGER IF EXISTS oilback.reserves_before_sales_insert$$   # 如果有的话就删除该触发器

CREATE  TRIGGER oilback.reserves_before_sales_insert
before INSERT ON sales_info FOR EACH ROW
BEGIN	
	if new.sales_amount>(select oil_reserves from oil_reserves_info where new.sales_oil_id=oil_reserves_info.oil_id and new.sales_deopt_id=oil_reserves_info.depot_oil_id)
    then signal sqlstate '45000' set message_text ='错误！对应油库已经没有足够的该类油出售！' ;
    else
		# 更新油库储量
		update oil_reserves_info
		set oil_reserves = oil_reserves - new.sales_amount
		where new.sales_deopt_id = oil_reserves_info.depot_oil_id and new.sales_oil_id = oil_reserves_info.oil_id;
        # 更新会员信息
        update member_info
        set member_all_speeding = member_all_speeding + new.sales_amount*new.sales_price*new.sales_discount
        where new.sales_customer = member_info.member_name;
       --  
--         if select member_all_speeding from member_info where new.sales_customer = member_info.member_name
--         then update ;
   end if;
 END$$
DELIMITER ;

DELIMITER $$
CREATE  TRIGGER oilback.member_after_update
after INSERT ON member_info FOR EACH ROW
BEGIN	
	if new.member_all_speeding>= 20000
    then  update member_info
    set member_level_id = 999   # 金卡对应的等级id
    where member_info.member_id=new.member_id;
    end if;
    if new.member_all_speeding >=10000 and new.member_all_speeding<20000
    then  update member_info
    set member_level_id = 666   # 星耀对应的等级id
    where member_info.member_id = new.member_id;
   end if;
 END$$
DELIMITER ;


/*
ALTER TABLE [dbo].[外键表] ADD
        CONSTRAINT [外键关系的名称 ] FOREIGN KEY
        (
                [外键]
        ) REFERENCES [dbo].[主键表] (
                [主键表名称]
        ) ON UPDATE CASCADE ON DELETE CASCADE
*/